const express = require('express')
const router = express.Router()
const path = require('path')
const db = require('../config/db')

let fileBaseUrl = ''
// 开发环境
if (process.env.ENV === 'dev') {
  fileBaseUrl = process.env.BASE_URL_DEV + '7a/api/'
} else {
  fileBaseUrl = process.env.BASE_URL + '7a/api/'
}

// 获取图片列表
router.get('/list', async (req, res) => {
  try {
    const {
      page = 1,
      pageSize = 20,
      filterType = 'all', // 'all' 或 'own'
      startTime,
      endTime,
      uploadUserId,
    } = req.query

    const params = {
      page: parseInt(page),
      pageSize: parseInt(pageSize),
      filterType,
      startTime,
      endTime,
      uploadUserId,
    }

    const result = await getPictureListFromDb(params)

    // 将字节转换为MB
    const dataWithMB = result.data.map((item) => ({
      ...item,
      file_size: item.file_size
        ? (item.file_size / (1024 * 1024)).toFixed(2)
        : '0.00',
    }))

    res.status(200).json({
      message: '获取图片列表成功',
      data: dataWithMB,
      pagination: result.pagination,
      code: 0,
    })
  } catch (error) {
    console.error('获取图片列表失败:', error)
    res.status(500).json({
      message: '获取图片列表失败',
      error: error.message,
      code: 500,
    })
  }
})

// 获取图片列表数据库操作
async function getPictureListFromDb(params) {
  const {
    page = 1,
    pageSize = 20,
    filterType = 'all',
    startTime,
    endTime,
    uploadUserId,
  } = params

  const offset = (page - 1) * pageSize
  const conditions = ['is_deleted = 0'] // 过滤已删除的图片
  const values = []

  // 筛选条件
  if (filterType === 'own' && uploadUserId) {
    conditions.push('upload_user_id = ?')
    values.push(uploadUserId)
  }

  // 时间范围筛选
  if (startTime) {
    conditions.push('upload_time >= ?')
    values.push(startTime)
  }

  if (endTime) {
    conditions.push('upload_time <= ?')
    values.push(endTime)
  }

  const whereClause =
    conditions.length > 0 ? 'WHERE ' + conditions.join(' AND ') : ''

  // 查询总数
  const countSql = `SELECT COUNT(*) as total FROM picture_bed ${whereClause}`
  const [countResult] = await db.query(countSql, values)
  const total = countResult[0].total

  // 查询数据
  const sql = `
    SELECT 
      id,
      file_name,
      original_name,
      file_size,
      file_type,
      file_format,
      width,
      height,
      url,
      local_path,
      upload_user_id,
      upload_ip,
      upload_user_agent,
      upload_time,
      access_count,
      last_access_time,
      created_at,
      updated_at
    FROM picture_bed
    ${whereClause}
    ORDER BY upload_time DESC
    LIMIT ? OFFSET ?
  `

  const queryValues = [...values, pageSize, offset]
  const [rows] = await db.query(sql, queryValues)

  // 计算分页信息
  const totalPages = Math.ceil(total / pageSize)

  return {
    data: rows,
    pagination: {
      page: parseInt(page),
      pageSize: parseInt(pageSize),
      total,
      totalPages,
      hasNext: page < totalPages,
      hasPrev: page > 1,
    },
  }
}

// 删除图片接口（软删除）
router.post('/delete', async (req, res) => {
  try {
    const { id, deleteUserId } = req.body

    if (!id) {
      return res.status(400).json({
        message: '图片ID不能为空',
        code: 400,
      })
    }

    // 获取客户端IP
    const deleteIp =
      req.headers['x-forwarded-for']?.split(',')[0] ||
      req.connection.remoteAddress ||
      req.socket.remoteAddress ||
      req.ip ||
      '0.0.0.0'

    // 软删除图片
    const success = await deletePictureFromDb(id, deleteUserId, deleteIp)

    if (!success) {
      return res.status(404).json({
        message: '图片不存在或无权限删除',
        code: 404,
      })
    }

    res.status(200).json({
      message: '图片删除成功',
      id: id,
      code: 0,
    })
  } catch (error) {
    console.error('删除图片失败:', error)
    res.status(500).json({
      message: '删除图片失败',
      error: error.message,
      code: 500,
    })
  }
})

// 删除图片数据库操作
async function deletePictureFromDb(id, deleteUserId = null, deleteIp = null) {
  const setParts = ['is_deleted = 1', 'delete_time = NOW()']
  const values = []

  // 如果提供了用户ID，则记录删除者信息
  if (deleteUserId) {
    setParts.push('delete_user_id = ?')
    values.push(deleteUserId)
  }

  // 如果提供了IP，则记录删除者IP
  if (deleteIp) {
    setParts.push('delete_ip = ?')
    values.push(deleteIp)
  }

  const sql = `UPDATE picture_bed SET ${setParts.join(', ')} WHERE id = ?`
  values.push(id)

  const [result] = await db.query(sql, values)
  return result.affectedRows > 0
}

// 图床上传接口
router.post('/upload', async (req, res) => {
  try {
    const {
      fileName,
      originalName,
      filePath,
      fileSize,
      fileType,
      fileFormat,
      width,
      height,
      uploadUserId,
    } = req.body

    // 验证必要参数
    if (!fileName || !originalName || !filePath || !fileSize) {
      return res.status(400).json({
        message: '缺少必要参数：fileName, originalName, filePath, fileSize',
        code: 400,
      })
    }

    // 获取客户端IP
    const uploadIp =
      req.headers['x-forwarded-for']?.split(',')[0] ||
      req.connection.remoteAddress ||
      req.socket.remoteAddress ||
      req.ip ||
      '0.0.0.0'

    // 保存到数据库
    const imageData = {
      fileName: fileName,
      originalName: originalName,
      filePath: filePath,
      fileSize: parseInt(fileSize),
      fileType:
        fileType || path.extname(originalName).toLowerCase().replace('.', ''),
      fileFormat:
        fileFormat ||
        path.extname(originalName).toLowerCase().replace('.', '').toUpperCase(),
      width: width || null,
      height: height || null,
      url: filePath,
      localPath: filePath,
      uploadUserId: uploadUserId || null,
      uploadIp: uploadIp,
      uploadUserAgent: req.get('User-Agent'),
    }

    const imageId = await addPictureToDb(imageData)

    res.status(200).json({
      message: '图片上传成功',
      data: {
        id: imageId,
        fileName: fileName,
        originalName: originalName,
        filePath: filePath,
        url: filePath,
        fileSize: fileSize,
        fileType: imageData.fileType,
        fileFormat: imageData.fileFormat,
        width: width,
        height: height,
      },
      code: 0,
    })
  } catch (error) {
    console.error('图片上传失败:', error)
    res.status(500).json({ message: error.message, code: 500 })
  }
})

// 数据库操作函数
async function addPictureToDb(imageData) {
  const sql = `
    INSERT INTO picture_bed (
      file_name, original_name, file_size, 
      file_type, file_format, width, height, url, local_path,
      upload_user_id, upload_ip, upload_user_agent
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `

  const values = [
    imageData.fileName,
    imageData.originalName,
    imageData.fileSize,
    imageData.fileType,
    imageData.fileFormat,
    imageData.width,
    imageData.height,
    imageData.url,
    imageData.localPath,
    imageData.uploadUserId,
    imageData.uploadIp,
    imageData.uploadUserAgent,
  ]

  const [result] = await db.query(sql, values)
  return result.insertId
}

module.exports = router
